package org.nobject.common.db.member;

import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.nobject.common.db.member.SqlWE.Compare;
import org.nobject.common.db.member.SqlWE.Relate;
import org.nobject.common.db.member.SqlWE.Type;
import org.nobject.common.exception.CreateException;
import org.nobject.common.lang.DateUtils;
import org.nobject.common.lang.StringUtils;

/**
 * SQL条件
 * SqlWhere sqlWhere=new SqlWhere()
 * 
 * SqlWE sqlWE=new SqlWE(Compare.equals,"userName","1234")
 * sqlWhere.add(Relate.OR,sqlWE)
 * 
 * SqlWhere sqlWhere2=new SqlWhere()
 * sqlWhere.add(Relate.AND,sqlWhere2)
 * 
 * sqlWhere.add(Relate.AND,"SELECT * FROM ")
 * 
 * sqlWhere.getSql();
 * TODO 暂时无法替换纯字符串的
 * @author bianrongjun
 * @version 1.0
 */
public class SqlWhere {

	/** relate */
	public Relate relate=Relate.AND;
	
	/**
	 * 构造函数 
	 * @param relate
	 */
	public SqlWhere(Relate relate){
		this.relate=relate;
	}
	
	/**
	 * 构造函数
	 */
	public SqlWhere() {

	}
	
	/**
	 * 构造函数 
	 * @param sqlWEs
	 */
	public SqlWhere(SqlWE[] sqlWEs) {
		for(SqlWE sqlWE:sqlWEs){
			this.add(sqlWE);
		}
	}
	
	/**
	 * 构造函数 
	 * @param sqlWEs
	 */
	public SqlWhere(Object[][] sqlWEs) {
		for(Object[] sqlWE:sqlWEs){
			this.add0((Relate)sqlWE[0],sqlWE[1]);
		}
	}
	
	/** 
	 * sqlWhereEles
	 * 每个元素是个二维数组，第一个是Relate,第二个是sqlWE(或sqlWhere或sql)
	 *  */
	public List<Object[]> relateSqlWEs = new LinkedList();
	
	/** 
	 * 查询SQLWhere中是否含有某字段
	 * TODO 无法处理纯字符串的
	 * @param column 字段
	 */
	public boolean contains(String column){
		for(Object[] sqlWhereEle:relateSqlWEs){
			Object sqlWE	=sqlWhereEle[1];
			if(sqlWE instanceof SqlWhere){
				if(((SqlWhere)sqlWE).contains(column)) return true;
			}else if(sqlWE instanceof SqlWE){
				if(((SqlWE)sqlWE).column.equals(column)) return true;
			}else if(sqlWE instanceof String){
				return false;
			}
		}
		return false;
	}
	
	/** 
	 * 替换已有的字段
	 * @param column
	 * @param nColumn
	 */
	public void replace(String column,String nColumn){
		SqlWE sqlWE=getSqlWE(column);
		if(sqlWE==null) return;
		sqlWE.column=nColumn;
	}
	
	/** 
	 * 删除含有的某字段
	 * @param column 字段
	 */
	public void remove(String column){
		for (int i = relateSqlWEs.size()-1; i >=0 ; i--) {
			Object[] relateSqlWE=relateSqlWEs.get(i);
			Object sqlWE=relateSqlWE[1];
			if(sqlWE instanceof SqlWhere){
				if(((SqlWhere)sqlWE).contains(column)){
					((SqlWhere)sqlWE).remove(column);
				}
			}else{
				if(((SqlWE)sqlWE).column.equals(column)){
					relateSqlWEs.remove(i);
					return;	
				}
			}
		}
	}
	
	/** 
	 * 获取SQLWE
	 * @param column 字段
	 */
	public SqlWE getSqlWE(String column){
		for(Object[] relateSqlWE:relateSqlWEs){
			Object sqlWE=relateSqlWE[1];
			if(sqlWE instanceof SqlWhere){
				SqlWE we=((SqlWhere)sqlWE).getSqlWE(column);
				if(we!=null) return we;
			}else if(sqlWE instanceof SqlWE){
				if(((SqlWE)sqlWE).column.equals(column)){
					return (SqlWE)sqlWE;	
				}
			}
		}
		return null;
	}
	
	/** 
	 * 获取值
	 * @param column
	 */
	public Object getValue(String column){
		SqlWE we=this.getSqlWE(column);
		return we==null?null:we.value;
	}
	
	/** 
	 * 添加SQL
	 * @param sql
	 */
	public void add(String sql) {
		sql=sql.trim();
		Relate relate=null;
		if(sql.toUpperCase().indexOf("OR ")==0||sql.toUpperCase().indexOf("AND ")==0){
			relate=sql.toUpperCase().substring(0,2).toUpperCase().equals("OR")?Relate.OR:Relate.AND;
		}
		if(relate==null) relate=Relate.AND;
		add(relate, sql);
	}
	
	/** 
	 * 添加SQL
	 * @param relate 关系
	 * @param sql SQL
	 */
	public void add(Relate relate,String sql) {
		relateSqlWEs.add(new Object[]{relate,sql});
	}
	
	
	/** 
	 * 添加SQLWE
	 * @param sqlWE
	 */
	public void add0(Relate relate,Object sqlWE) {
		relateSqlWEs.add(new Object[]{relate,sqlWE});
	}
	
	/** 
	 * 添加SQLWE
	 * @param sqlWE
	 */
	public void add(SqlWE sqlWE) {
		add(sqlWE.relate, sqlWE);
	}
	
	/** 
	 * 添加SQLWE
	 * @param sqlWE SQLWE对象
	 */
	public void add(Relate relate,SqlWE sqlWE) {
		relateSqlWEs.add(new Object[]{relate,sqlWE});
	}
	
	/** 
	 * 添加SQLWhere
	 * @param sqlWhere SQLWHERE对象
	 */
	public void add(SqlWhere sqlWhere){
		add(sqlWhere.relate,sqlWhere);
	}
	
	/** 
	 * 添加SQLWhere
	 * @param relate 关系
	 * @param sqlWhere SQLWHERE对象
	 */
	public void add(Relate relate,SqlWhere sqlWhere) {
		relateSqlWEs.add(new Object[]{relate,sqlWhere});
	}
	
	/**
	 * 创建
	 * 
	 * @param sqlWhereRule
	 * @param values
	 */
	public static SqlWhere create(SqlWE[] sqlWhereEles, Map<String,Object> values) throws CreateException {
		SqlWhere sqlWhere = new SqlWhere();
		if(values==null) return sqlWhere;
		
		
		for (String column : values.keySet()) {
			
			String strValue = StringUtils.toString0( values.get(column));
			
			//合并查询项
			//EXP: userName,userId => (userName LIKE '%2%' OR userId LIKE '%2%')
			if(column.indexOf(",")>0){
				
				//转化值
				Object value = convertValue(Type.STR,column,strValue);
				if (value == null) throw new CreateException("转化的值不能为空:" + column);
				
				SqlWhere sqlWhere2=new SqlWhere();
				
				//检测字段是否存在,并设置关系
				String[] cs=column.split(",");
				for(String c:cs){
					SqlWE sqlWhereEle = null;
					for (SqlWE swe : sqlWhereEles) {
						if(swe.column.equals(c)){
							sqlWhereEle = swe;
							break;
						}
					}
					if (sqlWhereEle == null) throw new CreateException("字段不存在:" + column);
					
					sqlWhereEle=new SqlWE(c,Compare.like,Relate.OR,sqlWhereEle.type,sqlWhereEle.separate);
					sqlWhereEle.value=value;
					sqlWhere2.add(sqlWhereEle);
				}
				sqlWhere.add(sqlWhere2);
			
			//单个查询项	
			}else{
				//检测SQLWHERE范围
				SqlWE sqlWhereEle = null;
				for (SqlWE swe : sqlWhereEles) {
					if(swe.column.equals(column)){
						sqlWhereEle = swe;
						break;
					}
				}
				
				if (sqlWhereEle == null) throw new CreateException("字段不存在:" + column);
				//不能直接用
				sqlWhereEle=sqlWhereEle.clone();
				
				
				Object value = convertValue(sqlWhereEle.type,sqlWhereEle.column,strValue);
				if (value == null) throw new CreateException("转化的值不能为空:" + column);
				sqlWhereEle.value = value;
				sqlWhere.add(sqlWhereEle);
			}
		}
		return sqlWhere;
	}
	
	/** 
	 * 转化值
	 * @param type 字段类型
	 * @param column 字段名
	 * @param strValue 字符串数值
	 */
	public static Object convertValue(Type type,String column,String strValue) throws CreateException{
		Object value = null;
		try {
			if 		(type.equals(SqlWE.Type.STR)){
				//if(SQLUtils.isDirty(strValue)) throw new CreateException("查询数据含有非法字符");
				value = strValue;
			}
			else if (type.equals(SqlWE.Type.NUM)) 	value = Integer.valueOf(strValue);
			else if (type.equals(SqlWE.Type.DATE)) 	value = DateUtils.toDate(strValue);
		} catch (Exception e) {
			e.printStackTrace();
			throw new CreateException("数值转化异常:" + column + "->" + strValue+":"+e.getMessage());
		}
		return value;
	}
	
	/** 获得该对象中已有的条件个数 */
	public int count() {
		return this.relateSqlWEs.size();
	}

	/** 获取SQL */
	public String getSql(Map<String, String> reflectTabColumns) {
		
		StringBuffer str = new StringBuffer();
		if (relateSqlWEs.size() == 0) return "";
		
		for(Object[] relateSqlWE:relateSqlWEs){
			Relate relate 	=(Relate)relateSqlWE[0];
			Object sqlWE	=relateSqlWE[1];
			if(sqlWE instanceof SqlWhere){
				str.append(" "+relate+" (").append(((SqlWhere)sqlWE).getSql(reflectTabColumns)).append(") ");
			}else if(sqlWE instanceof SqlWE){
				SqlWE we=(SqlWE)sqlWE;
				str.append(" "+relate+" ").append(we.getSql(reflectTabColumns.get(we.column))).append(" ");
			}else if(sqlWE instanceof String){
				str.append(" "+relate+" "+(String)sqlWE+" ");
			}else{
				throw new RuntimeException("暂不支持:"+sqlWE);
			}
		}
		
		//去掉第一个 OR 和  AND
		return str.toString().replaceFirst(" (\\w+) "," ");
	}
	
	/** 
	 * getSql
	 * @param preTab
	 */
	public String getSql(String preTab) {
		StringBuffer str = new StringBuffer();
		if (relateSqlWEs.size() == 0) return str.toString();
		
		for(Object[] relateSqlWE:relateSqlWEs){
			Relate relate 	=(Relate)relateSqlWE[0];
			Object sqlWE	=relateSqlWE[1];
			if(sqlWE instanceof SqlWhere){
				//TODO 这里的And要变成可传入的参数
				str.append(" "+relate+" (").append(((SqlWhere)sqlWE).getSql()).append(") ");
			}else if(sqlWE instanceof SqlWE){
				SqlWE we=(SqlWE)sqlWE;
				str.append(" "+relate+" ").append(we.getSql(preTab)).append(" ");
			}else if(sqlWE instanceof String){
				str.append(" "+relate+" "+(String)sqlWE+" ");
			}else{
				throw new RuntimeException("暂不支持:"+sqlWE);
			}
		}
		return str.toString().replaceFirst(" (\\w+) "," ");
	}
	
	public String getSql() {
		return this.getSql("");
	}
	
	/** 
	 * setPretab
	 * @param preTab
	 */
	public void setPretab(String preTab){
		for(Object sqlWE:relateSqlWEs){
			if(sqlWE instanceof SqlWhere){
				((SqlWhere)sqlWE).setPretab(preTab);
			}else{
				SqlWE we=(SqlWE)sqlWE;
				we.pre=preTab;
			}
		}
	}
	
	/** 
	 * setPretab
	 * @param columns
	 * @param preTab
	 */
	public void setPretab(String[] columns,String preTab){
		for(String column:columns){
			SqlWE sqlWhereEle=this.getSqlWE(column);
			if(sqlWhereEle==null) return;
			sqlWhereEle.pre=preTab;
		}
	}
	
	/** 是否为空 */
	public boolean empty() {
		return this.relateSqlWEs.size() == 0;
	}

	/** 清空 */
	public SqlWhere clear() {
		this.relateSqlWEs.clear();
		return this;
	}

//	// /** 添加LeftLike */
//	public SqlWhere addLeftLike(String column, String value) {
//		this.add(new SqlWhereEle(column, cmp, relate, type,value))
//	}
	// /** 添加RightLike */
	// public SqlWhere addRightLike(String column, String value)
	// {add(column+" LIKE '%"+value+"'");return this;}
	// /** 添加Like */
	// public SqlWhere addLike(String column, String value)
	// {add(column+" LIKE '%"+value+"%'");return this;}
	// /** 添加NotLike */
	// public SqlWhere addNotLike(String column, String value)
	// {add(column+" NOT LIKE '%"+value+"%'");return this;}
	// /** 添加相等 */
	// public SqlWhere addEqual(String column, Object value) {return
	// this.add(column, value, "=");}
	// /** 添加不等 */
	// public SqlWhere addNotEqual(String column, Object value) {return
	// this.add(column, value, "!=");}
	// /** 添加小于 */
	// public SqlWhere addLessThan(String column, Object value) {return
	// this.add(column, value, "<");}
	// /** 添加小于等于 */
	// public SqlWhere addLessOrEqual(String column, Object value){return
	// this.add(column, value, "<=");}
	// /** 添加大于 */
	// public SqlWhere addMoreThan(String column, Object value) {return
	// this.add(column, value, ">");}
	// /** 添加大于等于 */
	// public SqlWhere addMoreOrEqual(String column, Object value){return
	// this.add(column, value, ">=");}
	// /** 添加 in */
	// public SqlWhere addIn(String column, String value) {return
	// this.add(column + " in (" + value + ")");}
	// /** 添加 not in */
	// public SqlWhere addNotIn(String column, String value) {return
	// this.add(column + " not in (" + value+ ")");}
	// /** 添加Empty */
	// public SqlWhere addEmpty(String column) {return this.add(column +
	// " is null ");}
	// /** 添加 not Empty */
	// public SqlWhere addNotEmpty(String column) {return this.add(column +
	// " is not null ");}
	//
	// /** 添加表达式 */
	// private SqlWhere add(String str) {list.add(str);return this;}
	// /** 添加 */
	// public SqlWhere add(String key, Object value,String cmp){
	// if(ClassUtils.isString(value.getClass()))
	// add(key+cmp+"'"+value+"'");
	// else
	// add(key+cmp+StringUtils.toString0(value)+"");
	// return this;
	// }
}
